﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.Drawing;
using JTest.BUS;
using JTest.DTO;
using ExcelCOM = Microsoft.Office.Interop.Excel;
using System.IO;

namespace JTest
{
    public class wrongRecord
    {
        public string strLevel;
        public int intID;
        public string strQuestion;
        public string strA;
        public string strB;
        public string strC;
        public string strD;
        public string strAnswer;
        public string strType;
        public int intTimes;

        /// <summary>
        /// Contructor
        /// </summary>
        public wrongRecord()
        {
            strLevel = String.Empty;
            intID = -1;
            strQuestion = String.Empty;
            strAnswer = String.Empty;
            strType = String.Empty;
            intTimes = 1;
        }
    }
    public class grouping
    {
        public int intLevel;
        public string strType;
        public int times;
        public grouping()
        {
            intLevel = -1;
            strType = String.Empty;
            times = 1;
        }
    }
    public class Statistical
    {
        List<wrongRecord> WrongList = new List<wrongRecord>();
        public List<wrongRecord> getWrongRecord(string filename, ref string strmode, ref List<string> dbNameList, ref List<grouping> groupList)
        {
            try
            {
                grouping group = new grouping();
                PracticeLogDTO plDTO = PracticeLogBUS.loadPracticeLog(filename);
                int wAnswer = plDTO.WrongAnswerIDs.Length;
                int mode = plDTO.SettingPractice.Type;//TV or NP
                dbNameList.Add(plDTO.SettingPractice.DbName);
                group.intLevel = plDTO.SettingPractice.Level;
                if (mode == 1)
                    group.strType = "vocabulary";
                else
                    group.strType = "grammar";
                groupList.Add(group);

                if (mode == 1)
                    strmode = "vocabulary";
                else
                    strmode = "grammar";
                int level = plDTO.SettingPractice.Level;
                string strlevel = String.Empty;
                switch (level)
                {
                    case 1: strlevel = "N1";
                        break;
                    case 2: strlevel = "N2/N3";
                        break;
                    case 3: strlevel = "N4";
                        break;
                    default: strlevel = "N5";
                        break;
                }
                string dbName = plDTO.SettingPractice.DbName;
                OleDbConnection objConnect;
                string datapath = AppDomain.CurrentDomain.BaseDirectory + "App_Data\\" + dbName;
                string strConnectString = String.Empty;
                if (File.Exists(datapath))
                {
                    strConnectString = "Provider = Microsoft.Jet.OLEDB.4.0; Jet OLEDB:Database Password=" + JTest.Constant.DBPassWord + "; Data Source = " + datapath;
                }
                else
                {
                    strConnectString = command.GetTempDirectory();
                    command.extractDB(strConnectString);
                    strConnectString = "Provider = Microsoft.Jet.OLEDB.4.0; Jet OLEDB:Database Password=" + JTest.Constant.DBPassWord + "; Data Source = " + strConnectString + "\\data.dat";
                }
                objConnect = new OleDbConnection(strConnectString);//Tạo đối tượng Connect                
                objConnect.Open(); //Mở kết nối   
                string wrongsID = "(0";
                for (int i = 0; i < wAnswer; i++)
                {

                    wrongsID += "," + plDTO.WrongAnswerIDs.GetValue(i).ToString();//id cau sai
                }
                wrongsID += ")";
                string strSQL = @"SELECT * FROM " + strmode + "level" + level + " WHERE id in " + wrongsID;
                // Tạo Command
                OleDbCommand objCommand = new OleDbCommand();
                objCommand.Connection = objConnect;
                objCommand.CommandType = CommandType.Text;
                objCommand.CommandText = strSQL;

                // Tạo đối tượng Adapter
                OleDbDataAdapter objAdapter = new OleDbDataAdapter();
                objAdapter.SelectCommand = objCommand;//Nạp command cho DataAdapter

                // Tạo DataTable nhận dữ liệu trả về
                DataTable objDataTable = new DataTable("abc");
                objAdapter.Fill(objDataTable); //Điền dữ liệu trả về vào Table
                List<wrongRecord> wrongList = new List<wrongRecord>();
                for (int i = 0; i < objDataTable.Rows.Count; i++)
                {
                    wrongRecord rec = new wrongRecord();
                    object id = objDataTable.Rows[i].ItemArray.GetValue(0);
                    object question = objDataTable.Rows[i].ItemArray.GetValue(1);
                    object answerID = objDataTable.Rows[i].ItemArray.GetValue(6);
                    rec.intID = (int)id;
                    rec.strQuestion = question.ToString();
                    rec.strA = objDataTable.Rows[i].ItemArray.GetValue(2).ToString();
                    rec.strB = objDataTable.Rows[i].ItemArray.GetValue(3).ToString();
                    rec.strC = objDataTable.Rows[i].ItemArray.GetValue(4).ToString();
                    rec.strD = objDataTable.Rows[i].ItemArray.GetValue(5).ToString();
                    rec.strAnswer = objDataTable.Rows[i].ItemArray.GetValue(6).ToString();
                    rec.strLevel = strlevel;
                    rec.strType = strmode;

                    wrongList.Add(rec);
                }
                return wrongList;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }
        public bool isEqualRecord(wrongRecord a, wrongRecord b)
        {
            if (a.strType == b.strType)
                if (a.strLevel == b.strLevel)
                    if (a.intID == b.intID)
                        return true;
            return false;
        }
        private void SetColumnWidth(ExcelCOM.Worksheet ws, int col, int width)
        {
            ((ExcelCOM.Range)ws.Cells[1, col]).EntireColumn.ColumnWidth = width;
        }

        public void exportToExcelFile(string fileName, List<wrongRecord> wrongList, List<grouping> groupList)
        {
            //tao doi tuong excel
            ExcelCOM.Application exApp = new ExcelCOM.Application();
            ExcelCOM.Workbook exBook = exApp.Workbooks.Add(ExcelCOM.XlWBATemplate.xlWBATWorksheet);
            ExcelCOM.Worksheet exSheet = (ExcelCOM.Worksheet)exApp.Worksheets[1];
            exApp.Visible = false;
            //tao ten sheet
            DateTime dt = DateTime.Now;
            string exSheetName = String.Format("{0:ddMMyyyy}", dt);
            exSheet.Name = exSheetName;
            //tao header cho bang report          
            ExcelCOM.Range rSheetTitle = (ExcelCOM.Range)exSheet.get_Range("A1", "L1");
            rSheetTitle.Select();
            rSheetTitle.Merge(true);
            rSheetTitle.HorizontalAlignment = ExcelCOM.XlHAlign.xlHAlignCenter;
            rSheetTitle.Font.Bold = true;
            rSheetTitle.Font.Size = 16;
            rSheetTitle.Value2 = "不正解リスト";

            //Range of Column's title
            //Put text in cellules
            ExcelCOM.Range rColumnHead = (ExcelCOM.Range)exSheet.get_Range("A2", "L2");
            ExcelCOM.Range rHeadTitleContent;
            rColumnHead.HorizontalAlignment = ExcelCOM.XlHAlign.xlHAlignCenter;
            rHeadTitleContent = (ExcelCOM.Range)rColumnHead[1, 1];
            rHeadTitleContent.Value2 = "番目";
            rHeadTitleContent = (ExcelCOM.Range)rColumnHead[1, 2];
            rHeadTitleContent.Value2 = "形";
            rHeadTitleContent = (ExcelCOM.Range)rColumnHead[1, 3];
            rHeadTitleContent.Value2 = "水準";
            rHeadTitleContent = (ExcelCOM.Range)rColumnHead[1, 4];
            rHeadTitleContent.Value2 = "ID";
            rHeadTitleContent = (ExcelCOM.Range)rColumnHead[1, 5];
            rHeadTitleContent.Value2 = "問題";
            rHeadTitleContent = (ExcelCOM.Range)rColumnHead[1, 6];
            rHeadTitleContent.Value2 = "A";
            rHeadTitleContent = (ExcelCOM.Range)rColumnHead[1, 7];
            rHeadTitleContent.Value2 = "B";
            rHeadTitleContent = (ExcelCOM.Range)rColumnHead[1, 8];
            rHeadTitleContent.Value2 = "C";
            rHeadTitleContent = (ExcelCOM.Range)rColumnHead[1, 9];
            rHeadTitleContent.Value2 = "D";
            rHeadTitleContent = (ExcelCOM.Range)rColumnHead[1, 10];
            rHeadTitleContent.Value2 = "正解";
            rHeadTitleContent = (ExcelCOM.Range)rColumnHead[1, 11];
            rHeadTitleContent.Value2 = "%";
            rHeadTitleContent = (ExcelCOM.Range)rColumnHead[1, 12];
            rHeadTitleContent.Value2 = "備考";

            //Thay doi chieu rong cot
            SetColumnWidth(exSheet, 1, 5);
            SetColumnWidth(exSheet, 2, 10);
            SetColumnWidth(exSheet, 3, 5);
            SetColumnWidth(exSheet, 4, 5);
            SetColumnWidth(exSheet, 5, 100);

            //to mau va dong khung header
            for (int i = 1; i <= rColumnHead.Columns.Count; i++)
            {
                ExcelCOM.Range rCell = (ExcelCOM.Range)rColumnHead[1, i];
                rCell.BorderAround(ExcelCOM.XlLineStyle.xlContinuous, ExcelCOM.XlBorderWeight.xlThin,
                ExcelCOM.XlColorIndex.xlColorIndexAutomatic, ColorTranslator.ToWin32(Color.Black));
                rCell.Interior.Color = ColorTranslator.ToWin32(Color.Yellow);
            }

            // write wrong record to excel file.
            int currentPoint = 1; //Chỉ số dòng hiện tại
            foreach (wrongRecord rec in wrongList)
            {
                rHeadTitleContent = (ExcelCOM.Range)rColumnHead[currentPoint + 1, 1];
                rHeadTitleContent.Value2 = currentPoint;
                rHeadTitleContent = (ExcelCOM.Range)rColumnHead[currentPoint + 1, 2];
                rHeadTitleContent.Value2 = rec.strType;
                rHeadTitleContent = (ExcelCOM.Range)rColumnHead[currentPoint + 1, 3];
                rHeadTitleContent.Value2 = rec.strLevel;
                rHeadTitleContent = (ExcelCOM.Range)rColumnHead[currentPoint + 1, 4];
                rHeadTitleContent.Value2 = rec.intID; rHeadTitleContent.Interior.Color = ColorTranslator.ToWin32(Color.GreenYellow);
                rHeadTitleContent = (ExcelCOM.Range)rColumnHead[currentPoint + 1, 5];
                rHeadTitleContent.Value2 = rec.strQuestion;
                rHeadTitleContent = (ExcelCOM.Range)rColumnHead[currentPoint + 1, 6];
                rHeadTitleContent.Value2 = rec.strA;
                rHeadTitleContent = (ExcelCOM.Range)rColumnHead[currentPoint + 1, 7];
                rHeadTitleContent.Value2 = rec.strB;
                rHeadTitleContent = (ExcelCOM.Range)rColumnHead[currentPoint + 1, 8];
                rHeadTitleContent.Value2 = rec.strC;
                rHeadTitleContent = (ExcelCOM.Range)rColumnHead[currentPoint + 1, 9];
                rHeadTitleContent.Value2 = rec.strD;
                rHeadTitleContent = (ExcelCOM.Range)rColumnHead[currentPoint + 1, 10];
                rHeadTitleContent.Value2 = rec.strAnswer;

                switch (rec.strAnswer)//To mau cau tra loi dung
                {
                    case "A":
                        rHeadTitleContent = (ExcelCOM.Range)rColumnHead[currentPoint + 1, 6];
                        rHeadTitleContent.Interior.Color = ColorTranslator.ToWin32(Color.LightBlue);
                        break;
                    case "B":
                        rHeadTitleContent = (ExcelCOM.Range)rColumnHead[currentPoint + 1, 7];
                        rHeadTitleContent.Interior.Color = ColorTranslator.ToWin32(Color.LightBlue);
                        break;
                    case "C":
                        rHeadTitleContent = (ExcelCOM.Range)rColumnHead[currentPoint + 1, 8];
                        rHeadTitleContent.Interior.Color = ColorTranslator.ToWin32(Color.LightBlue);
                        break;
                    case "D":
                        rHeadTitleContent = (ExcelCOM.Range)rColumnHead[currentPoint + 1, 9];
                        rHeadTitleContent.Interior.Color = ColorTranslator.ToWin32(Color.LightBlue);
                        break;
                    default:
                        break;
                }

                // Tinh % sai.
                double percent = 0;
                string note = "'";
                #region <tính phần trăm>

                #endregion

                for (int i = 0; i < groupList.Count; i++)
                {
                    if (rec.strType.Trim() == groupList[i].strType.Trim())
                    {
                        if (CovertLevelToInt(rec.strLevel) == groupList[i].intLevel)
                        {
                            percent = ((double)rec.intTimes / groupList[i].times) * 100;
                            note += rec.intTimes.ToString() + "/" + groupList[i].times.ToString();
                            break;
                        }
                    }

                }
                string pc = String.Format("{0:0.00000000}", percent);
                int index = pc.IndexOf(".00");
                if (index > 0)
                    pc = pc.Remove(index);
                rHeadTitleContent = (ExcelCOM.Range)rColumnHead[currentPoint + 1, 11];
                rHeadTitleContent.Value2 = pc + "%";
                rHeadTitleContent = (ExcelCOM.Range)rColumnHead[currentPoint + 1, 12];
                rHeadTitleContent.Value2 = note;
                currentPoint++;
            }

            exBook.SaveAs(fileName, ExcelCOM.XlFileFormat.xlWorkbookDefault,
                             null, null, false, false,
                            ExcelCOM.XlSaveAsAccessMode.xlExclusive,
                            false, false, false, false, false);

            exBook.Close(false, false, false);
            exApp.Quit();

            //Unload Comobject
            System.Runtime.InteropServices.Marshal.ReleaseComObject(exBook);
            System.Runtime.InteropServices.Marshal.ReleaseComObject(exApp);
            System.Runtime.InteropServices.Marshal.ReleaseComObject(exSheet);
        }
        public static int CovertLevelToInt(string strlevel)
        {
            if (strlevel.Trim() == "N1")
                return 1;
            if (strlevel.Trim() == "N2/N3")
                return 2;
            if (strlevel.Trim() == "N4")
                return 3;
            if (strlevel.Trim() == "N5")
                return 4;
            return -1;
        }
    }
}
